package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import po.PlanItemPO;
import dbservice.DatabaseService;


public class PlanDatabase extends InitDatabase implements DatabaseService<PlanItemPO>{

	@Override
	public PlanItemPO find(int id) {
		PlanItemPO planItemPO = null;
		String sql = "select * from planItem where planID=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				planItemPO = new PlanItemPO();
				planItemPO.setCourseName(rs.getString("courseName"));
				planItemPO.setPlanTerm(rs.getInt("planTerm"));
				planItemPO.setPlanID(id);
				planItemPO.setCourseCredit(rs.getInt("courseCredit"));
				planItemPO.setPlanDepartment(rs.getString("planDepartment"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return planItemPO;
	}

	@Override
	public int insert(PlanItemPO t) {
		int planID = 0;
		String sql = "insert into planItem(courseName, courseCredit, planTerm, planDepartment) values(?,?,?,?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, t.getCourseName());
			ps.setInt(2, t.getCourseCredit());
			ps.setInt(3, t.getPlanTerm());
			ps.setString(4, t.getPlanDepartment());
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if(rs.next()) {
				planID = rs.getInt(1);
				t.setPlanID(planID);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return planID;
	}

	@Override
	public int update(PlanItemPO t) {
		int result = 0;
		String sql = "update planItem set courseName=?, courseCredit=?, planTerm=?  where " +
				"planID=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, t.getCourseName());
			ps.setInt(2, t.getCourseCredit());
			ps.setInt(3, t.getPlanTerm());
			ps.setInt(4, t.getPlanID());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delete(int id) {
		int result = 0;
		String sql = "delete from planItem where planID=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

}
